import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

# 定义文件路径
file_paths = {
    'EUR/CNY': 'D:/pythondate/exchange rate/eur_cny.csv',
    'EUR/USD': 'D:/pythondate/exchange rate/eur_usd.csv', 
    'USD/CNY': 'D:/pythondate/exchange rate/usd_cny.csv'
}

# 1. 检查缺失值
print("缺失值检查结果:")
for name, path in file_paths.items():
  df = pd.read_csv(path)
  missing = df.isnull().sum()
  print(f"{name}:")
  print(missing)
  print("-" * 30)

# 2. 可视化汇率走势
plt.figure(figsize=(15, 10))

for i, (name, path) in enumerate(file_paths.items(), 1):
  df = pd.read_csv(path, parse_dates=['date'])
  df.sort_values('date', inplace=True)
  
  # 找出最高点和最低点
  max_rate = df['exchange_rate'].max()
  min_rate = df['exchange_rate'].min()
  max_date = df.loc[df['exchange_rate'].idxmax(), 'date']
  min_date = df.loc[df['exchange_rate'].idxmin(), 'date']
  # 绘制汇率曲线
  plt.subplot(3, 1, i)
  plt.plot(df['date'], df['exchange_rate'], label=name)
  
  # 标记最高点和最低点
  plt.scatter(max_date, max_rate, color='red', label=f'highest: {max_rate:.4f}')
  plt.scatter(min_date, min_rate, color='green', label=f'lowest: {min_rate:.4f}')
  
  # 设置图表格式
  plt.title(f'{name} exchange rate trend')
  plt.xlabel('Date')
  plt.ylabel('exchange_rate')
  plt.legend()
  plt.gca().xaxis.set_major_formatter(mdates.DateFormatter('%Y-%m'))
  plt.gca().xaxis.set_major_locator(mdates.MonthLocator(interval=3))
  plt.grid(True)

plt.tight_layout()
plt.show()




#合并三个文件并保存
all_data = pd.DataFrame()
for name, path in file_paths.items():  
  df = pd.read_csv(path)
  df['currency_pair'] = name  # 添加标识列
  all_data = pd.concat([all_data, df], ignore_index=True)

# 转换数据格式：行为货币对，列为日期
pivot_data = all_data.pivot(index='date', 
                            columns='currency_pair', 
                            values='exchange_rate')

# 按日期排序
pivot_data = pivot_data.sort_index()

# 保存转换格式后的文件
pivot_data.to_csv('D:/pythondate/exchange rate/all_pivoted.csv')
print("转换格式后的文件已保存为: D:/pythondate/exchange rate/all_pivoted.csv")


# 新增机器学习预测代码
from sklearn.ensemble import RandomForestRegressor
from sklearn.svm import SVR
from xgboost import XGBRegressor
from sklearn.metrics import mean_absolute_error, mean_squared_error
from sklearn.model_selection import train_test_split
import numpy as np

# 准备数据
def prepare_data(series, n_lags=7):
    X, y = [], []
    for i in range(len(series)-n_lags-3):
       window = series[i:i+n_lags]
       target = series[i+n_lags:i+n_lags+3]
       if not np.isnan(window).any() and not np.isnan(target).any(): # 检查NaN
       X.append(window)
       y.append(target)
       return np.array(X), np.array(y)

# 对每种货币进行预测
print("\n机器学习模型预测结果:")
for currency in pivot_data.columns:
  print(f"\n=== {currency} ===")
  series = pivot_data[currency].values

# 准备训练数据
  X, y = prepare_data(series)
  if len(X) == 0 or len(y) == 0: # 检查是否有有效数据
    print(f"警告: {currency} 没有足够有效数据用于训练")
  continue
  
  X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, shuffle=False)
  
   # 定义模型
  models = {
    "随机森林": RandomForestRegressor(n_estimators=100),
    "支持向量机": SVR(kernel='rbf', C=100),
    "XGBoost": XGBRegressor(n_estimators=100)
  }
 
  # 训练和预测
  for name, model in models.items():
    try:
      model.fit(X_train, y_train[:,0]) # 只预测第1天
      pred = model.predict(X_test[-1:]) # 用最后7天预测未来
      mae = mean_absolute_error(y_test[:,0], model.predict(X_test))
      rmse = np.sqrt(mean_squared_error(y_test[:,0], model.predict(X_test)))
      print(f"{name}预测未来3天: {[round(pred[0],4)]*3}")
      print(f"{name} MAE误差: {mae:.4f}, RMSE误差: {rmse:.4f}")
    except Exception as e:
      print(f"{name} 训练失败: {str(e)}")


import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates

# 定义文件路径
file_paths = {
  'EUR/CNY': 'D:/pythondate/exchange rate/eur_cny.csv',
  'EUR/USD': 'D:/pythondate/exchange rate/eur_usd.csv', 
  'USD/CNY': 'D:/pythondate/exchange rate/usd_cny.csv'
}

# 1. 检查缺失值
print("缺失值检查结果:")
for name, path in file_paths.items():
  df = pd.read_csv(path)
  missing = df.isnull().sum()
  print(f"{name}:")
  print(missing)
  print("-" * 30)

# 2新增代码：合并三个文件并保存
all_data = pd.DataFrame()
for name, path in file_paths.items():
  df = pd.read_csv(path)
  df['currency_pair'] = name #  添加标识列
  all_data = pd.concat([all_data, df], ignore_index=True)

# 转换数据格式：行为货币对，列为日期
pivot_data = all_data.pivot(index='date', 
                            columns='currency_pair', 
                            values='exchange_rate')

# 按日期排序
pivot_data = pivot_data.sort_index()

# 保存转换格式后的文件
pivot_data.to_csv('D:/pythondate/exchange rate/all_pivoted.csv')
print("转换格式后的文件已保存为: D:/pythondate/exchange rate/all_pivoted.csv")

# 新增机器学习预测代码
from sklearn.ensemble import RandomForestRegressor, GradientBoostingRegressor
from sklearn.svm import SVR
from xgboost import XGBRegressor
from sklearn.metrics import mean_absolute_error, mean_squared_error
from sklearn.model_selection import train_test_split
import numpy as np

# 准备数据
def prepare_data(series, n_lags=7):
  X, y = [], []
  for i in range(len(series)-n_lags-3):
    window = series[i:i+n_lags]
    target = series[i+n_lags:i+n_lags+3]
    if not np.isnan(window).any() and not np.isnan(target).any():  # 检查NaN
    X.append(window)
    y.append(target)
  return np.array(X), np.array(y)

# 对每种货币进行预测
print("\n机器学习模型预测结果:")
for currency in pivot_data.columns:
  print(f"\n=== {currency} ===")
  series = pivot_data[currency].values
  
  # 准备训练数据
  X, y = prepare_data(series)
  if len(X) == 0 or len(y) == 0:  # 检查是否有有效数据
    print(f"警告: {currency} 没有足够有效数据用于训练")
    continue   
  
  X_train, X_test, y_train, y_test = train_test_split(X, y, test_size=0.2, shuffle=False)

# 定义模型
models = {
 "随机森林": RandomForestRegressor(n_estimators=100),
 "支持向量机": SVR(kernel='rbf', C=100),
 "XGBoost": XGBRegressor(n_estimators=100),
 "梯度提升": GradientBoostingRegressor(n_estimators=100)
}
  
  # 训练和预测
for name, model in models.items():
    try:
        model.fit(X_train, y_train[:,0])  # 只预测第1天
        pred = model.predict(X_test[-1:])  # 用最后7天预测未来
        mae = mean_absolute_error(y_test[:,0], model.predict(X_test))
        rmse = np.sqrt(mean_squared_error(y_test[:,0], model.predict(X_test)))
        print(f"{name}预测未来3天: {[round(pred[0],4)]*3}")
        print(f"{name} MAE误差: {mae:.4f}, RMSE误差: {rmse:.4f}")
        print("-" * 30)
        print(f"{name} MAE误差: {mae:.4f}, RMSE误差: {rmse:.4f}")

           # 可视化预测结果
            plt.figure(figsize=(12, 6))
            
            # 设置中文字体
            plt.rcParams['font.sans-serif'] = ['SimHei']  # 用来正常显示中文标签
            plt.rcParams['axes.unicode_minus'] = False  # 用来正常显示负号
            
            plt.plot(y_test[:,0], label='实际值')
            plt.plot(model.predict(X_test), label='预测值')
            plt.title(f'{currency}汇率预测 - {name}')
            plt.xlabel('样本索引')
            plt.ylabel('汇率')
            plt.legend()
            plt.grid(True)
            plt.show()
        except Exception as e:
            print(f"{name} 训练失败: {str(e)}")

import pandas as pd
from pandasai import SmartDataFrame
from pandasai.llm.openai import OpenAI

# 读取 CSV 文件
df = pd.read_csv(r"D:\pythondate\exchangerate\usd_cny.csv")  # 汇率文件的路径

# 检查数据是否为空
if df.empty:
    print("读取的 CSV 文件为空，请检查文件内容。")
else:
    # 将 Date 列转换为日期时间格式
    df['Date'] = pd.to_datetime(df['Date'])

    # 设置 Date 列为索引，同时保留 Date 作为普通列
    df.set_index('Date', inplace=False)
    # 设置 Date 列为索引，同时保留 Date 作为普通列
    df = df.set_index("Date", inplace=False)

    # 配置 DeepSeek 模型
    OpenAI_supported_chat_models.append('deepseek-chat')
    llm_openai = OpenAI(
        api_base='https://api.deepseek.com',
        api_token='sk-a78b6f4b5cf849319ec23a8671a05d0f',
        model='deepseek-chat'
    )

    # 创建 SmartDataFrame
    smart_df = SmartDataFrame(df, config={"llm": llm_openai})

    # 编写 smart_df.chat 内容以获取每列数据类型
    response = smart_df.chat("基于汇率数据，"
                             "请分析中美贸易摩擦事件对两国货币汇率的影响："
                             "1. 识别2018年3月和2019年5月的汇率数据"
                             "2. 比较事件当月与前一个月的汇率均值"
                             "3. 分析事件前后对汇率的影响"
                             "请用中文简洁回答，使用基础统计方法即可。")
    print(response)
import pandas as pd
from pandasai import SmartDataFrame
from pandasai.llm.openai import OpenAI

# 读取 CSV 文件
df = pd.read_csv(r"D:\pythondate\exchangerate\usd_cny.csv")  # 汇率文件的路径

# 检查数据是否为空
if df.empty:
    print("读取的 CSV 文件为空，请检查文件内容。")
else:
    # 将 Date 列转换为日期时间格式
    df['Date'] = pd.to_datetime(df['Date'])

    # 设置 Date 列为索引，同时保留 Date 作为普通列
    df.set_index('Date', inplace=False)
    # 设置 Date 列为索引，同时保留 Date 作为普通列
    df = df.set_index("Date", inplace=False)

    # 配置 DeepSeek 模型
    OpenAI_supported_chat_models.append('deepseek-chat')
    llm_openai = OpenAI(
        api_base='https://api.deepseek.com',
        api_token='sk-a78b6f4b5cf849319ec23a8671a05d0f',
        model='deepseek-chat'
    )

    # 创建 SmartDataFrame
    smart_df = SmartDataFrame(df, config={"llm": llm_openai})

    # 编写 smart_df.chat 内容以获取每列数据类型
    response = smart_df.chat("基于汇率数据，"
                             "请分析中美贸易摩擦事件对两国货币汇率的影响："
                             "1. 识别2018年3月和2019年5月的汇率数据"
                             "2. 比较事件当月与前一个月的汇率均值"
                             "3. 分析事件前后对汇率的影响"
                             "请用中文简洁回答，使用基础统计方法即可。")
    print(response)